-- ============================================
-- PostgreSQL 数据库建表脚本 - 消息通知模块
-- 包含：消息通知表
-- 创建时间：2025/11/7
-- ============================================

-- 创建消息通知表
-- 用于存储系统发送给用户的各类消息通知
CREATE TABLE IF NOT EXISTS message (
    id BIGSERIAL PRIMARY KEY,                                -- 消息ID，自增主键
    user_id BIGINT NOT NULL,                                 -- 用户ID，关联用户表
    title VARCHAR(100) NOT NULL,                             -- 消息标题
    content VARCHAR(500) NOT NULL,                           -- 消息内容
    type INTEGER NOT NULL,                                   -- 消息类型：1-系统通知，2-订单消息，3-预约消息，4-活动消息
    is_read INTEGER DEFAULT 0,                               -- 是否已读：0-未读，1-已读
    related_id BIGINT,                                       -- 关联ID（订单ID、预约ID等）
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- 创建时间
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP          -- 更新时间
);

-- 添加表注释
COMMENT ON TABLE message IS '消息通知表';
COMMENT ON COLUMN message.id IS '消息ID';
COMMENT ON COLUMN message.user_id IS '用户ID';
COMMENT ON COLUMN message.title IS '消息标题';
COMMENT ON COLUMN message.content IS '消息内容';
COMMENT ON COLUMN message.type IS '消息类型：1-系统通知，2-订单消息，3-预约消息，4-活动消息';
COMMENT ON COLUMN message.is_read IS '是否已读：0-未读，1-已读';
COMMENT ON COLUMN message.related_id IS '关联ID（订单ID、预约ID等）';
COMMENT ON COLUMN message.create_time IS '创建时间';
COMMENT ON COLUMN message.update_time IS '更新时间';

-- 创建索引（提高查询性能）
CREATE INDEX IF NOT EXISTS idx_message_user_id ON message(user_id);
CREATE INDEX IF NOT EXISTS idx_message_is_read ON message(is_read);
CREATE INDEX IF NOT EXISTS idx_message_type ON message(type);
CREATE INDEX IF NOT EXISTS idx_message_create_time ON message(create_time);

-- 创建联合索引（优化未读消息查询）
CREATE INDEX IF NOT EXISTS idx_message_user_read ON message(user_id, is_read);

-- 创建更新时间触发器
CREATE OR REPLACE FUNCTION update_message_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_message_timestamp
    BEFORE UPDATE ON message
    FOR EACH ROW
    EXECUTE FUNCTION update_message_timestamp();

-- ============================================
-- 建表完成
-- ============================================
